using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyKTV
{
    class SongDao
    {

        private DataTable querySongByCondtion(String conditionSql)
        {
            string sql = "select Song.Id,Song.Name,Song.PlayUrl,Song.PlayCount,Song.PinYin,SongType.TypeName as songTypeName,Singer.PhotoUrl,Singer.Name as SingerName ,SingerType.TypeName as singerTypeName,Singer.Gender " +
                "from Song,SongType ,Singer,SingerType " +
                "where  Song.SongTypeId=SongType.Id and Song.SingerId=Singer.Id and SingerType.Id=Singer.SingerTypeId and " +
                conditionSql;
            Console.WriteLine("sql=" + sql);
          DataTable dataTable=  DBHelper.getDataTable(sql);
          return dataTable;
        }

        /*按拼音查询歌曲*/
        public DataTable getSongListByPinYin(string pinYin)
        {
            String condition="Song.PinYin like '%{0}%'";
            return querySongByCondtion(String.Format(condition, pinYin));
        }

        /*按歌手名称查询歌曲*/
        public DataTable getSongListBySingerName(string singerName)
        {
            String condition = "Singer.Name like '%{0}%'";
            return querySongByCondtion(String.Format(condition, singerName));
        }

        /*按歌手的类型查询歌曲*/
        public DataTable getSongListBySingerTypeId(int singerTypeId)
        {
            String condition = "Singer.SingerTypeId ={0}";
            return querySongByCondtion(String.Format(condition, singerTypeId));
        }


        /*按歌曲类型查询歌曲*/

        public DataTable getSongListBySongTypeId(int songTypeId)
        {
            String condition = "SongType.Id={0}";
            return querySongByCondtion(String.Format(condition, songTypeId));
        }

        /*按排行获取歌曲*/
        public DataTable getSongListOrderByPlayCount()
        {
            String condition = " 1=1 order by Song.PlayCount desc ";
            return querySongByCondtion(condition);
        }

        /*按字数歌曲*/
        public DataTable getSongListByWordCount(int wordCount)
        {
            String condition = " len(Song.Name)={0} ";
            return querySongByCondtion(String.Format(condition, wordCount));
        }


        public Song getSongById(int id)
        {

          DataTable dataTable=  this.querySongByCondtion("Song.Id=" + id);
          if (dataTable.Rows.Count == 0)
          {
              //没有
              return null;
          }
          DataRow dataRow=dataTable.Rows[0];
          Song song = new Song();
          dataRow["Id"].ToString();
          song.id= int.Parse(dataRow["Id"].ToString());
          song.name = dataRow["Name"].ToString();
          song.playUrl = dataRow["PlayUrl"].ToString();
          song.pinYin = dataRow["PinYin"].ToString();
          song.playCount = int.Parse(dataRow["playCount"].ToString());
          song.singerGender = dataRow["Gender"].ToString();
          song.singerName = dataRow["SingerName"].ToString();
          song.singerTypeName = dataRow["singerTypeName"].ToString();
          song.songTypeName = dataRow["songTypeName"].ToString();
          song.phtoUrl = dataRow["PhotoUrl"].ToString();
          Console.WriteLine(song.id);
          return song;
        }
    }
}
